﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using DefektaiApp.Properties;
using ewal.Msg;
using ewal.Data;

namespace DefektaiApp
{    
    public partial class frmAllPlaces : Form
    {       
        private BindingSource bindingSource;
        private DbDataAdapter dAdapter;
        private DbConnection conn;
        private DataTable dTable;
        private DataView dView;
        private string savedFilter = string.Empty;
        private string savedOrder = string.Empty;
        bool hasChanges;
        private string mainSql;
        List<string> filterElements = new List<string>();
        ArrayList rikiavimai = new ArrayList();
        string cc = " AND "; // current connector

        struct Vieta
        {
            private int m_index;
            private int m_id;
            private int m_linijaId;
            private string m_stotisId;
            private int m_kelias;
            private int m_siule;
            private long m_metrai;
            private DateTime m_rasta_data;

            public Vieta(DataRow row, int index)
            {
                this.m_index = index;
                this.m_id = Convert.ToInt32(row["id"]);
                this.m_linijaId = Convert.ToInt32(row["linijaId"]);
                this.m_stotisId = row["stotisId"].ToString();
                this.m_kelias = Convert.ToInt32(row["kelias"]);
                this.m_siule = Convert.ToInt32(row["siule"]);
                this.m_metrai = Convert.ToInt64(row["metrai"]);
                this.m_rasta_data = (DateTime)(row["rasta_data"]);
            }

            public int index
            {
                get { return m_index; }
            }
            public int id
            {
                get { return m_id; }
            }
            public int linijaId
            {
                get { return m_linijaId; }
            }
            public string stotisId
            {
                get { return m_stotisId; }
            }
            public int kelias
            {
                get { return m_kelias; }
            }
            public int siule
            {
                get { return m_siule; }
            }
            public long metrai
            {
                get { return m_metrai; }
            }
            public DateTime rasta_data
            {
                get { return m_rasta_data; }
            }
        }
        struct QueryParam
        {
            private string parameterName;
            private string sourceColumnName;
            private DbType valueType;

            internal QueryParam(string paramName, string columnName, DbType typeOfValue)
            {
                parameterName = paramName;
                sourceColumnName = columnName;
                valueType = typeOfValue;
            }

            public string Vardas
            {
                get { return parameterName; }
                set { parameterName = value; }
            }

            public string Stulpelis
            {
                get { return sourceColumnName; }
                set { sourceColumnName = value; }
            }

            public DbType Tipas
            {
                get { return valueType; }
                set { valueType = value; }
            }
        }
        struct Rikiavimas
        {
            internal string columnName;
            internal bool ascending;

            internal Rikiavimas(string coln, bool scend)
            {
                columnName = coln;
                ascending = scend;
            }
        }
        enum ColumnType { intg, date, text, booln };    

        public frmAllPlaces()
        {
            InitializeComponent();
            dAdapter = DbHelper.DPFactory.CreateDataAdapter();
            conn = DbHelper.DPFactory.CreateConnection();
            conn.ConnectionString = DbHelper.ConnectionString;
            dAdapter.SelectCommand = DbHelper.DPFactory.CreateCommand();
            dAdapter.SelectCommand.Connection = conn;
            dTable = new DataTable();
            bindingSource = new BindingSource();
            buildUpdateCommand();
            buildDeleteCommand();
        }

        private void frmAllPlaces_Load(object sender, EventArgs e)
        {
            mainSql = "SELECT ";
            mainSql += "Pranesimai.id, ";
            mainSql += "Pranesimai.k11, Pranesimai.k12, Pranesimai.k21, Pranesimai.k22, Pranesimai.k23, Pranesimai.k24, Pranesimai.k31, Pranesimai.k32, Pranesimai.k41, Pranesimai.k42, Pranesimai.k51, ";
            mainSql += "Pranesimai.d55pd, Pranesimai.d70p, Pranesimai.d70a, Pranesimai.d0, Pranesimai.d42p, Pranesimai.d42a, Pranesimai.d55ad, Pranesimai.d55an, ";
            mainSql += "Pranesimai.pastaba, Pranesimai.iteikti_data, Pranesimai.iteikti_operId, Pranesimai.iteikti_dfId, ";
            mainSql += "Pranesimai.rasta_dfId, Pranesimai.rasta_data, Pranesimai.defFailoNr, Pranesimai.atspausdinta, Pranesimai.spausdinta_data, Pranesimai.zurnale, Pranesimai.rezultatas, ";
            mainSql += "Operatoriai.vardas AS iteikti_operV FROM Pranesimai LEFT JOIN Operatoriai ON Pranesimai.iteikti_operId=Operatoriai.id;";
            dAdapter.SelectCommand.CommandText = mainSql;
            tstbPaklaida.Text = UserSettings.Default.Paklaida.ToString();
            setupDfpaiCombo();
            setupOpriaiCombo();
            doColumns();
            query();
        }

        private void setupDfpaiCombo()
        {
            setupToolStripComboBox(
                tscbIteikti_dfIds, "SELECT id FROM Defektoskopai WHERE isActive=TRUE ORDER BY id", "id", "id", true);
        }

        private void setupOpriaiCombo()
        {
            setupToolStripComboBox(
                tscbIteikti_operIds, "SELECT id, vardas FROM Operatoriai WHERE isActive=TRUE ORDER BY vardas", "vardas", "id", true);
        }

        private void query()
        {
            int topRow = 0;
            int selectedRow = -1;
            try
            {
                topRow = dgvAllPlaces.FirstDisplayedScrollingRowIndex;
                selectedRow = dgvAllPlaces.SelectedRows[0].Index;
            }
            catch // (Exception e1)
            {
                // Msg.ErrorMsg(e1.Message);
            }

            try
            {savedFilter = dView.RowFilter;}
            catch
            {savedFilter = string.Empty;}

            try
            {savedOrder = dView.Sort;}
            catch
            {savedOrder = "id desc";}

            dTable.Clear();
            dAdapter.Fill(dTable);
            dView = new DataView(dTable, savedFilter, savedOrder, DataViewRowState.CurrentRows);
            bindingSource.DataSource = dView;
            dgvAllPlaces.DataSource = bindingSource;
            

            hasChanges = false;
            tsbSave.Enabled = false;

            try
            {
                dgvAllPlaces.FirstDisplayedScrollingRowIndex = topRow;
                dgvAllPlaces.Rows[selectedRow].Selected = true;
            }
            catch // (Exception e2)
            {
                // Msg.ErrorMsg(e2.Message);
                // Nesigaus, tai nesigaus...
            }


            // Čia kažkoks bybis - po update dgvDefektas rodo pirmojo įrašo duomenis, o ne to, kuris selected=true
            dgvDefektas.DataSource = bindingSource;
            updateStatusBar();
        }

        private void doColumns()
        {
            dgvAllPlaces.AutoGenerateColumns = false;
            dgvDefektas.AutoGenerateColumns = false;

            foreach (DataGridViewColumn column in dgvAllPlaces.Columns)
            {
                column.DataPropertyName = column.Name;
            }

            foreach (DataGridViewColumn column in dgvDefektas.Columns)
            {
                column.DataPropertyName = column.Name;
            }

            dgvAllPlaces.Columns["rezultatas"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            dgvDefektas.Columns["pastaba"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
        }

        private void setupToolStripComboBox(
                    ToolStripComboBox combo,
                    string sqlSelectStatement,
                    string displayMemberName,
                    string valueMemberName,
                    bool emptyrow)
        {
            DataTable dTable = DbHelper.FillDataTable(sqlSelectStatement);
            if (emptyrow)
            {
                DataRow emptyRow = dTable.NewRow();
                if (displayMemberName != "id")
                {
                    emptyRow[displayMemberName] = string.Empty;
                }
                else
                {
                    emptyRow[displayMemberName] = DBNull.Value;
                }
                emptyRow[valueMemberName] = DBNull.Value;
                dTable.Rows.InsertAt(emptyRow, 0);
            }
            combo.ComboBox.DataSource = dTable;
            combo.ComboBox.DisplayMember = displayMemberName;
            combo.ComboBox.ValueMember = valueMemberName;
        }               


        # region filtravimas

        private void prefilter(string format)
        {
            // šitas metodas iš parinktų dgv langelių atrenka tuos, kurie
            // dalyvaus sudarant filtrą

            DataGridViewSelectedCellCollection selCells = dgvAllPlaces.SelectedCells;
            if (selCells.Count == 0) return;

            // langelius į sąrašą, kad būtų galima manipuliuoti
            List<DataGridViewCell> list = new List<DataGridViewCell>();
            foreach (DataGridViewCell cl in selCells)
            {
                list.Add(cl);
            }

            // sąraše langeliai išrikiuojami 
            list.Sort(new DGVCellsComparer());

            int nonFiltered = dgvAllPlaces.Columns["id"].Index;

            int currentColumnIndex = -1;
            for (int i = 0; i < list.Count; i++)
            {
                if (list[i].ColumnIndex == nonFiltered)
                    continue; //praleisti id stulpelį

                // imti kiekvieno stulpelio aukščiausiąjį langelį:
                if (list[i].ColumnIndex != currentColumnIndex) // jeigu tai naujas stulpelis,...
                {
                    currentColumnIndex = list[i].ColumnIndex; // ... jį padaryti senu...
                    // ... ir su juo daryti filtro elementą
                    filterElements.Add(string.Format(format, makeFilterByCell(list[i])));
                }
            }
        }

        private void doFilter()
        {
            if (filterElements.Count > Settings.Default.MaxFilterParts)
            {
                Msg.ExclamationMsg(string.Format(Messages.Per_ilgas_filtras, Settings.Default.MaxFilterParts));
                while (filterElements.Count > Settings.Default.MaxFilterParts)
                    filterElements.RemoveAt(filterElements.Count - 1);
            }
            dView.RowFilter = String.Join(cc, filterElements);
            updateStatusBar();
        }

        private string makeFilterByCell(DataGridViewCell cl)
        {
            string columnName = cl.DataGridView.Columns[cl.ColumnIndex].Name;
            switch (columnName)
            {
                case "k11":
                case "k12":
                case "k21":
                case "k22":
                case "k23":
                case "k24":
                case "k31":
                case "k32":
                case "k41":
                case "k42":
                case "k51":
                case "rasta_dfId":
                case "iteikti_operId":
                    return makeFilterString(columnName, cl.Value, ColumnType.intg);

                case "iteikti_operV":
                case "defFailoNr":
                case "rezultatas":
                    return makeFilterString(columnName, cl.Value, ColumnType.text);

                case "rasta_data":
                case "iteikti_data":
                case "spausdinta_data":
                    return makeFilterString(columnName, cl.Value, ColumnType.date);

                case "iteikti_dfId":
                    return makeFilterString(columnName, cl.FormattedValue, ColumnType.intg);

                case "atspausdinta":
                case "zurnale":
                    return makeFilterString(columnName, cl.Value, ColumnType.booln);

                default:
                    return string.Empty;
            }
        }

        private string makeFilterString(string parameterName, object cellValue, ColumnType type)
        {
            //if (cellValue == null) return string.Format("{0} IS NULL", parameterName);

            switch (type)
            {
                case ColumnType.intg:
                    int id;
                    try
                    {
                        id = Convert.ToInt32(cellValue);
                        return string.Format("{0}={1}", parameterName, id.ToString());
                    }
                    catch
                    {
                        return string.Format("{0} IS NULL", parameterName);
                    }

                case ColumnType.date:
                    DateTime dt;
                    try
                    {
                        dt = Convert.ToDateTime(cellValue);
                        return string.Format("{0}={1}", parameterName, DbHelper.FormatDateValue(dt));
                    }
                    catch
                    {
                        return string.Format("{0} IS NULL", parameterName);
                    }

                case ColumnType.text:
                    string st;
                    try
                    {
                        st = cellValue.ToString();
                        if (st.Length > 0)
                        {
                            return string.Format("{0}='{1}'", parameterName, st);
                        }
                        else
                        {
                            return string.Format("({0}='' OR {0} IS NULL)", parameterName);
                        }
                    }
                    catch
                    {
                        return string.Format("({0}='' OR {0} IS NULL)", parameterName);
                    }

                case ColumnType.booln:
                    bool bl;
                    try
                    {
                        bl = Convert.ToBoolean(cellValue);
                        return string.Format("{0}={1}", parameterName, bl.ToString());
                    }
                    catch
                    {
                        return string.Format("{0} IS NULL", parameterName);
                    }

                default:
                    return string.Empty;
            }
        }

        private void unfilter()
        {
            // numeta filtrą ir rūšiavimą
            dView.RowFilter = string.Empty;
            filterElements.Clear();
            updateStatusBar();
        }

        private void tsbFilterBy_Click(object sender, EventArgs e)
        {
            // filtruoti įrašus pagal pasirinktų langelių reikšmes
            prefilter("{0}");
            doFilter();
        }

        private void tsbNegFilterBy_Click(object sender, EventArgs e)
        {
            // neigiamas filtras pagal pasirinktų langelių reikšmes
            prefilter("NOT({0})");
            doFilter();
        }
               
        private void tsmiDropFilterTail_Click(object sender, EventArgs e)
        {
            if (filterElements.Count > 0)
                filterElements.RemoveAt(filterElements.Count - 1);
            doFilter();
        }

        private void tsmiDropFilter_Click(object sender, EventArgs e)
        {
            unfilter();
        }

        private void tsbUnfilter_Click(object sender, EventArgs e)
        {
            // numeta filtrą ir rūšiavimą
            unfilter();

        }

        # endregion


        #region rikiavimas

        private void preOrder(bool ascending)
        {
            // šitas metodas iš parinktų dgv langelių atrenka tuos stulpelius, kurie
            // dalyvaus sudarant rikiavimą 

            DataGridViewSelectedCellCollection selCells = dgvAllPlaces.SelectedCells;
            if (selCells.Count == 0) return;

            // langelius į sąrašą, kad būtų galima manipuliuoti
            List<DataGridViewCell> list = new List<DataGridViewCell>();
            foreach (DataGridViewCell cl in selCells)
            {
                list.Add(cl);
            }

            // sąraše langeliai išrikiuojami 
            list.Sort(new DGVCellsComparer());

            int currentColumnIndex = -1;
            for (int i = 0; i < list.Count; i++)
            {
                // imti kiekvieno stulpelio pirmąjį langelį:
                if (list[i].ColumnIndex != currentColumnIndex) // jeigu tai naujas stulpelis,...
                {
                    currentColumnIndex = list[i].ColumnIndex; // ... jį padaryti senu...
                    // ... ir su juo daryti filtro elementą
                    rikiavimai.Add(new Rikiavimas(dgvAllPlaces.Columns[list[i].ColumnIndex].Name, ascending));
                }
            }
        }

        private void doOrder()
        {
            string sortOrder = string.Empty;
            foreach (Rikiavimas rik in rikiavimai)
            {
                sortOrder += rik.columnName + (rik.ascending ? string.Empty : " DESC") + ", ";
            }

            dView.Sort = sortOrder.Remove(sortOrder.Length - 2);
            updateStatusBar();
        }

        private void tsbAZ_Click(object sender, EventArgs e)
        {
            preOrder(true);
            doOrder();
        }

        private void tsbZA_Click(object sender, EventArgs e)
        {
            preOrder(false);
            doOrder();
        }

        private void tsbDropOrder_Click(object sender, EventArgs e)
        {
            rikiavimai.Clear();
            dView.Sort = "";
            updateStatusBar();
        }

        private void customOrder(string[] paras)
        {
            foreach (Rikiavimas rik in rikiavimai)
            {
                foreach (string colname in paras)
                {
                    if (rik.columnName == colname)
                    {
                        Msg.ExclamationMsg(string.Format(Messages.Pagal_toki_jau_rikiuota, colname));
                        return;
                    }
                }
            }

            for (int i = 0; i < paras.Length; i++)
            {
                rikiavimai.Add(new Rikiavimas(paras[i], true));
            }
            doOrder();
        }

        #endregion


        #region įrašų žymėjimas

        private void tsmiMarkNotConfirmed_Click(object sender, EventArgs e)
        {
            // pažymėti įrašą kaip nepasitvirtinusį

            if (dgvAllPlaces.CurrentCell.RowIndex < 0) return;

            endEditAndSave();

            string updateStatement = string.Format("UPDATE Pranesimai SET rezultatas='{0}' WHERE id={1};",
                Messages.RecordDisproved,
                dgvAllPlaces.Rows[dgvAllPlaces.CurrentCell.RowIndex].Cells["id"].Value.ToString());

            DbHelper.ExecuteNonQuery(updateStatement);

            query();
        }

        private void tsmiMarkUnexamined_Click(object sender, EventArgs e)
        {
            // pažymėti įrašą kaip nepatikrintą ir sukurti kopiją kitam patikrinimui
            if (dgvAllPlaces.CurrentCell.RowIndex < 0) return;
            endEditAndSave();
            string id = dgvAllPlaces.Rows[dgvAllPlaces.CurrentCell.RowIndex].Cells["id"].Value.ToString();

            string updateStatement = string.Format("UPDATE Pranesimai SET rezultatas='{0}' WHERE id={1};",
                Messages.RecordUnExamined, id);
            // MessageBox.Show(updateStatement);
            DbHelper.ExecuteNonQuery(updateStatement);

            StringBuilder sb = new StringBuilder("INSERT INTO Pranesimai (");
            sb.Append("k11, k12, k21, k22, k23, k24, k31, k32, k41, k42, k51, ");
            sb.Append("d55pd, d70p, d70a, d42p, d42a, d0, d55ad, d55an, pastaba, ");
            sb.Append("iteikti_data, iteikti_operId, iteikti_dfId, rasta_dfId, ");
            sb.Append("rasta_data, defFailoNr, atspausdinta, spausdinta_data, zurnale, rezultatas) ");
            sb.Append("SELECT ");
            sb.Append("k11, k12, k21, k22, k23, k24, k31, k32, k41, k42, k51, ");
            sb.Append("d55pd, d70p, d70a, d42p, d42a, d0, d55ad, d55an, pastaba, ");
            sb.Append("NULL, NULL, NULL, rasta_dfId, ");
            sb.Append("rasta_data, defFailoNr, False, NULL, NULL, 'KARTOJAMA id" + id + "' ");
            //sb.Append("iteikti_data, iteikti_operId, iteikti_dfId, rasta_dfId, ");
            //sb.Append("rasta_data, defFailoNr, atspausdinta, spausdinta_data, rezultatas ");

            sb.Append("FROM Pranesimai WHERE id=");
            sb.Append(id);
            sb.Append(";");
            // MessageBox.Show (sb.ToString());
            DbHelper.ExecuteNonQuery(sb.ToString());

            query();
        }

        private void tsmiMarkRegistered_Click(object sender, EventArgs e)
        {
            // pažymėti įrašą kaip jau užregistruotą defektą
            endEditAndSave();
            if (dgvAllPlaces.CurrentCell.RowIndex < 0) return;

            string updateStatement = string.Format("UPDATE Pranesimai SET rezultatas='{0}', atspausdinta=True WHERE id={1};",
                Messages.RecordRegisteredDefect,
                dgvAllPlaces.Rows[dgvAllPlaces.CurrentCell.RowIndex].Cells["id"].Value.ToString());
            // MessageBox.Show(updateStatement);
            DbHelper.ExecuteNonQuery(updateStatement);

            query();
        }

        private void tscbIteikti_dfIds_DropDownClosed(object sender, EventArgs e)
        {
            // parinktų eilučių iteikti_dfId laukeliams parenkama ta reikšmė,
            // kokią rodo tscbIteikti_dfIds
            foreach (DataGridViewRow row in dgvAllPlaces.SelectedRows)
            {
                row.Cells["iteikti_dfId"].Value = tscbIteikti_dfIds.ComboBox.SelectedValue;
                //endEditAndSave();
            }
        }

        private void tscbIteikti_operIds_DropDownClosed(object sender, EventArgs e)
        {
            // parinktų eilučių iteikti_operId laukeliams parenkama ta reikšmė,
            // kokią rodo tscbIteikti_operIds
            foreach (DataGridViewRow row in dgvAllPlaces.SelectedRows)
            {
                row.Cells["iteikti_operId"].Value = tscbIteikti_operIds.ComboBox.SelectedValue;
                row.Cells["iteikti_operV"].Value = tscbIteikti_operIds.ComboBox.Text;
                //endEditAndSave();
            }
        }

        private void dtpIteikti_data_Validated(object sender, EventArgs e)
        {
            if (dtpIteikti_data.Checked)
            {
                foreach (DataGridViewRow row in dgvAllPlaces.SelectedRows)
                {
                    row.Cells["iteikti_data"].Value = dtpIteikti_data.Value.ToString("yyyy-MM-dd");
                    //endEditAndSave();
                }
            }
            else
            {
                foreach (DataGridViewRow row in dgvAllPlaces.SelectedRows)
                {
                    row.Cells["iteikti_data"].Value = DBNull.Value;
                    //endEditAndSave();
                }
            }
        }

        #endregion


        #region dalykų tvarkymas

        private void tsmiManageOperators_Click(object sender, EventArgs e)
        {
            // tvarkyti operatorius
            endEditAndSave();
            using (TvarkytiOperatoriai frmOperatoriai = new TvarkytiOperatoriai())
            {
                frmOperatoriai.ShowDialog();
            }
            setupOpriaiCombo();
        }

        private void tsmiManageDefectoscopes_Click(object sender, EventArgs e)
        {
            // tvarkyti defektoskopus
            endEditAndSave();
            using (TvarkytiDefektoskopai frmDskopai = new TvarkytiDefektoskopai())
            {
                frmDskopai.ShowDialog();
            }
            setupDfpaiCombo();
        }

        #endregion


        #region darbas su vienu įrašu

        private void tsbNaujas_Click(object sender, EventArgs e)
        {
            endEditAndSave();
            using (frmSingleRecord frmsinglerecord = new frmSingleRecord())
            {
                frmsinglerecord.ShowDialog();
            }
            query();
        }

        private void tsbEdit_Click(object sender, EventArgs e)
        {
            endEditAndSave();
            long currentRecordId = Convert.ToInt64(dgvAllPlaces.CurrentRow.Cells["id"].Value);
            using (frmSingleRecord frmsinglerecord = new frmSingleRecord(currentRecordId))
            {
                frmsinglerecord.ShowDialog();
            }
            query();
        }

        #endregion


        private void tsmiQuit_Click(object sender, EventArgs e)
        {
            if (hasChanges)
            {
                DialogResult result = Msg.OkCancelQuestion("Yra pakeitimų. Ar išsaugoti?");

                if (result == DialogResult.No)  // pakeitimų yra, bet neišsaugoti
                {
                    Close();
                }
                else if (result == DialogResult.Cancel) // pakeitimų yra, bet nutraukti veiksmą
                {
                    return;
                }
                else   // pakeitimų yra ir išsaugoti
                {
                    endEditAndSave();
                    Close();
                }
            }
            else // pakeitimų nėra - išeiti ir viskas
            {
                Close();
            }
        }

        private void tsmiSaveChanges_Click(object sender, EventArgs e)
        {
            
        }

        private void tsmiPrintRecords_Click(object sender, EventArgs e)
        {
            // paleisti nespausdintų pranešimų formą
            endEditAndSave();

            using (frmRecordsToPrint rtpForm = new frmRecordsToPrint())
            {
                rtpForm.ShowDialog();
            }
            query();
        }

        private string sanit(object controlValue)
        {
            // išvalo stringus nuo sql neleistinų simbolių - ' ir "
            // (nors gal būtų galima escapinti?)
            string strToSanitize = string.Empty;
            try
            {
                strToSanitize = controlValue.ToString();
            }
            catch
            {
                return string.Empty;
            }

            Regex forb = new Regex("['\"]*");
            return forb.Replace(strToSanitize, string.Empty).Trim();
        }
        
        private void endEditAndSave()
        {
            //MessageBox.Show("Įrašomi atlikti pakeitimai...");
            dgvAllPlaces.EndEdit(DataGridViewDataErrorContexts.Commit);
            bindingSource.EndEdit();
            dAdapter.Update(dTable);

            hasChanges = false;
            tsbSave.Enabled = false;
        }

        private void dgvAllPlaces_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            
            // jeigu įvedant duomenis į dgvAllPlaces padaroma klaida
            if (e.Exception != null)
            {
                if (e.Context == DataGridViewDataErrorContexts.Commit)
                {
                    Msg.WarningMsg(Messages.Paskutinio_lang_reiksme_nebus_pakeista);
                    dgvAllPlaces.CancelEdit();
                }
                else
                {
                    Msg.WarningMsg(Messages.Netinka_reiksme);
                }
            }
        }        

        private void tsmiDupPlace_Click(object sender, EventArgs e)
        {
            
            // parodo tuos įrašus, kuriuose kartojasi vieta
            int paklaida = UserSettings.Default.Paklaida;

            // parsisiuncia visus pranesimus, isrikiuotus pagal liniją, stotį, kelią, siūlę
            DataTable allRecords = DbHelper.FillDataTable("SELECT id, linijaId, stotisId, kelias, siule, (km*1000 + (pk-1)*100 + m) AS metrai, rasta_data  FROM Pranesimai ORDER BY linijaId, stotisId, kelias, siule, rasta_data");


            // suskirsto pranešimus į samePlacesList'us pagal vienodus linija-stotis-kelias-siule.
            // jeigu samePlacesList'e yra daugiau kaip vienas pranesimas, toks samePlaceList'as išsaugomas listOfSamePlaces'e

            ArrayList samePlacesList = new ArrayList(); 
            ArrayList listsOfSamePlaces = new ArrayList();
            Vieta cVt = new Vieta(allRecords.Rows[0], 0);

            for (int i = 1; i < allRecords.Rows.Count; i++)
            {
                Vieta vt = new Vieta(allRecords.Rows[i], i);
                if ((cVt.linijaId != vt.linijaId) ||
                    (cVt.stotisId != vt.stotisId) ||
                    (cVt.kelias != vt.kelias) ||
                    (cVt.siule != vt.siule))
                {
                    if (samePlacesList.Count > 1)
                    {
                        listsOfSamePlaces.Add(samePlacesList);
                    }
                    samePlacesList = new ArrayList();
                    cVt = vt;
                }
                samePlacesList.Add(vt);
            }

            
            // iš listsOfSamePlaces ima kiekvieną vienodų vietų sąrašą,
            // tikrina ar tame sąraše yra panašių vietų pagal km-pk-m,
            // panašių vietų id išsaugo duplicatesList'e.
            // jeigu duplicatesList'o ilgis daugiau už 1, 
            // tokio duplicatesList'o turinį išsaugo allDuplicates'e
            // (išsaugo tik tuos duplicatesList'o id, kurių allDuplicates'e dar nebuvo)

            ArrayList duplicatesList = new ArrayList();
            ArrayList allDuplicates = new ArrayList();
            foreach (ArrayList spList in listsOfSamePlaces)
            {
                for (int j = 0; j < spList.Count - 1; j++)
                {
                    duplicatesList.Add(((Vieta)spList[j]).id);
                    for (int k = j + 1; k < spList.Count; k++)
                    {
                        if (Math.Abs(((Vieta)spList[k]).metrai - ((Vieta)spList[j]).metrai) <= paklaida &&
                            ((Vieta)spList[j]).rasta_data != ((Vieta)spList[k]).rasta_data)
                        {
                            duplicatesList.Add(((Vieta)spList[k]).id);
                        }
                    }
                    if (duplicatesList.Count > 1)
                    {
                        foreach (int id in duplicatesList)
                        {
                            if (allDuplicates.IndexOf(id) == -1)
                            allDuplicates.Add(id);
                        }                        
                    }
                    duplicatesList = new ArrayList();
                }
            }

            // tikrinama, ar panašių vietų rasta apskritai
            if (allDuplicates.Count <1)
            {
                Msg.InformationMsg(Messages.Panasiu_vietu_nera);
                return;
            }

            // jeigu panašių vietų rasta, sąrašas dView filtruojamas ir rikiuojamas
            StringBuilder sbFilter = new StringBuilder();
            foreach (int id in allDuplicates)
            {
                sbFilter.Append(string.Format("id={0} OR ", id));
            }
            sbFilter.Remove(sbFilter.Length - 4, 4);
            string strSort = "linijaId, stotisId, kelias, km, pk, m, siule, rasta_data";

            dView.RowFilter = sbFilter.ToString();
            dView.Sort = strSort;
        }               

        private void dgvAllPlaces_CurrentCellChanged(object sender, EventArgs e)
        {
            if (dgvAllPlaces.CurrentCell == null)
            {
                tscbIteikti_dfIds.ComboBox.SelectedIndex = -1;
                tscbIteikti_operIds.ComboBox.SelectedIndex = -1;
                return;
            }
            tscbIteikti_dfIds.ComboBox.SelectedValue = dgvAllPlaces.Rows[dgvAllPlaces.CurrentCell.RowIndex].Cells["iteikti_dfId"].Value;
            tscbIteikti_operIds.ComboBox.SelectedValue = dgvAllPlaces.Rows[dgvAllPlaces.CurrentCell.RowIndex].Cells["iteikti_operId"].Value;
            try
            {
                dtpIteikti_data.Value = (DateTime)dgvAllPlaces.Rows[dgvAllPlaces.CurrentCell.RowIndex].Cells["iteikti_data"].Value;
                dtpIteikti_data.Checked = true;
            }
            catch
            {
                dtpIteikti_data.Checked = false;
            }
        }        

        private void tstbPaklaida_TextChanged(object sender, EventArgs e)
        {
            try
            {
                int paklaida = Convert.ToInt32(tstbPaklaida.Text);
                if (paklaida < 0 && paklaida > 200)
                {
                    Msg.InformationMsg(Messages.Paklaida_turi_buti);
                }
                else
                {
                    UserSettings.Default.Paklaida = paklaida;
                }
            }
            catch
            {
                Msg.InformationMsg(Messages.Paklaida_turi_buti);
            }
        }        

        private void frmAllPlaces_FormClosing(object sender, FormClosingEventArgs e)
        {
            endEditAndSave();
        }

        private void dgvAllPlaces_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            hasChanges = true;
            tsbSave.Enabled = true;
        }

        private void updateStatusBar()
        {
            lblStatusFilter.Text = "Filtras: " + dView.RowFilter + "  Rikiavimas: " + dView.Sort;
            lblStatus.Text = "Įrašų: " + dView.Count.ToString();
            lblStatusSelected.Text = "Pažymėta: " + dgvAllPlaces.SelectedRows.Count.ToString();
        }        

        private void buildUpdateCommand()
        {
            StringBuilder updSql = new StringBuilder("UPDATE Pranesimai SET ");
            updSql.Append("iteikti_operId=@iteikti_operId, ");
            updSql.Append("iteikti_dfId=@iteikti_dfId, ");
            updSql.Append("iteikti_data=@iteikti_data, ");
            updSql.Append("atspausdinta=@atspausdinta, ");
            updSql.Append("spausdinta_data=@spausdinta_data, ");
            updSql.Append("zurnale=@zurnale, ");
            updSql.Append("rezultatas=@rezultatas ");
            updSql.Append("WHERE id=@id;");

            DbCommand updateCommand = DbHelper.DPFactory.CreateCommand();
            updateCommand.CommandText = updSql.ToString();

            QueryParam[] parametrai = {new QueryParam("@iteikti_operId", "iteikti_operId", DbType.Int32),
                                      new QueryParam("@iteikti_dfId", "iteikti_dfId", DbType.Int32),
                                      new QueryParam("@iteikti_data", "iteikti_data", DbType.Date),
                                      new QueryParam("@atspausdinta", "atspausdinta", DbType.Boolean),
                                      new QueryParam("@spausdinta_data", "spausdinta_data", DbType.Date),
                                      new QueryParam("@zurnale", "zurnale", DbType.Boolean),
                                      new QueryParam("@rezultatas", "rezultatas", DbType.String),
                                      new QueryParam("@id", "id", DbType.Int32)};
            DbParameter p;
            foreach (QueryParam parametras in parametrai)
            {
                p = DbHelper.DPFactory.CreateParameter();
                p.ParameterName = parametras.Vardas;
                p.SourceColumn = parametras.Stulpelis;
                p.DbType = parametras.Tipas;
                updateCommand.Parameters.Add(p);
            }
            dAdapter.UpdateCommand = updateCommand;
            dAdapter.UpdateCommand.Connection = conn;
        }

        private void buildDeleteCommand()
        {
            StringBuilder updSql = new StringBuilder("DELETE FROM Pranesimai WHERE id=@id;");

            DbCommand deleteCommand = DbHelper.DPFactory.CreateCommand();
            deleteCommand.CommandText = updSql.ToString();

            QueryParam[] parametrai = {new QueryParam("@id", "id", DbType.Int32)};
            DbParameter p;
            foreach (QueryParam parametras in parametrai)
            {
                p = DbHelper.DPFactory.CreateParameter();
                p.ParameterName = parametras.Vardas;
                p.SourceColumn = parametras.Stulpelis;
                p.DbType = parametras.Tipas;
                deleteCommand.Parameters.Add(p);
            }
            dAdapter.DeleteCommand = deleteCommand;
            dAdapter.DeleteCommand.Connection = conn;
        }

        private void tsbExport_ButtonClick(object sender, EventArgs e)
        {

        }

        private void tsbSave_Click(object sender, EventArgs e)
        {
            // išsaugoti visus pakeitimus
            endEditAndSave();
        }

        private void dgvAllPlaces_SelectionChanged(object sender, EventArgs e)
        {
            lblStatusSelected.Text = "Pažymėta: " + dgvAllPlaces.SelectedRows.Count.ToString();
        }      
        
       }
    class DGVCellsComparer : IComparer<DataGridViewCell>
    {
        #region IComparer<DataGridViewCell> Members

        public int Compare(DataGridViewCell cl1, DataGridViewCell cl2)
        {
            // ASC pagal stulpelius ir tada ASC pagal eilutes
            int returnValue = 1;
            if (cl1 != null && cl2 == null)
            {
                returnValue = 0;
            }
            else if (cl1 == null && cl2 != null)
            {
                returnValue = 0;
            }
            else if (cl1 != null && cl2 != null)
            {
                if (cl1.ColumnIndex.Equals(cl2.ColumnIndex))
                {
                    returnValue = cl1.RowIndex.CompareTo(cl2.RowIndex);
                }
                else
                {
                    returnValue = cl1.ColumnIndex.CompareTo(cl2.ColumnIndex);
                }
            }
            return returnValue;
        }

        #endregion
    }
}
